import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_毕业去向分布各类型总体占比(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    毕业去向分布各类型总体占比 - 指标计算函数
    
    ## 指标说明
    计算不同毕业去向类型的学生占比，包括单位就业、自主创业、自由职业、升学、待就业、暂不就业等类型。
    返回两级数据：
    1. 一级分类汇总（grad_dest_merge）：升学、单位就业等大类的人数及占比
    2. 二级分类明细（grad_dest）：境内升学、境外留学、签就业协议形式就业等详细类型的人数及占比
    基于学生客观表统计数据，计算各类型人数占总毕业生人数的比例。
    数据按人数降序排序，便于查看主要去向类型。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出：总体数据（包含一级和二级分类）
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": [
            {
                "group": "总体",
                "data": [
                    {
                        "一级分类": "单位就业",
                        "一级人数": 1300,
                        "一级占比": 0.65,
                        "明细": [
                            {
                                "二级分类": "签就业协议形式就业",
                                "人数": 800,
                                "占比": 0.4
                            },
                            {
                                "二级分类": "签劳动合同形式就业",
                                "人数": 500,
                                "占比": 0.25
                            }
                        ]
                    },
                    {
                        "一级分类": "升学",
                        "一级人数": 500,
                        "一级占比": 0.25,
                        "明细": [
                            {
                                "二级分类": "境内升学",
                                "人数": 380,
                                "占比": 0.19
                            },
                            {
                                "二级分类": "境外留学",
                                "人数": 120,
                                "占比": 0.06
                            }
                        ]
                    },
                    {
                        "一级分类": "待就业",
                        "一级人数": 180,
                        "一级占比": 0.09,
                        "明细": [
                            {
                                "二级分类": "待就业",
                                "人数": 180,
                                "占比": 0.09
                            }
                        ]
                    },
                    {
                        "一级分类": "自主创业",
                        "一级人数": 20,
                        "一级占比": 0.01,
                        "明细": [
                            {
                                "二级分类": "自主创业",
                                "人数": 20,
                                "占比": 0.01
                            }
                        ]
                    }
                ],
                "total": 2000
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 毕业去向分布各类型总体占比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 构建学生表名
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        
        logger.info("开始计算总体的毕业去向分布")
        
        # 4. 查询毕业去向分布数据（包含一级和二级分类）
        # 查询二级明细数据（grad_dest + grad_dest_merge）
        detail_sql = f"""
        SELECT
            grad_dest_merge,
            grad_dest,
            COUNT(*) as student_count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY grad_dest_merge, grad_dest
        ORDER BY grad_dest_merge, grad_dest
        """
        detail_results = db.fetchall(detail_sql, (shard_tb_key,))
        
        if not detail_results:
            raise ValueError("未找到毕业去向分布数据")
        
        # 计算总人数
        total_students = sum(item['student_count'] for item in detail_results)
        if total_students == 0:
            raise ValueError("毕业生总人数为0")
        
        # 按一级分类分组统计
        category_dict = {}
        for item in detail_results:
            merge_type = item['grad_dest_merge']
            detail_type = item['grad_dest']
            count = item['student_count']
            
            if merge_type not in category_dict:
                category_dict[merge_type] = {
                    "count": 0,
                    "details": []
                }
            
            category_dict[merge_type]["count"] += count
            category_dict[merge_type]["details"].append({
                "二级分类": detail_type,
                "人数": count,
                "占比": round(count / total_students, 4)
            })
        
        # 构建结果数据
        data = []
        for merge_type, category_info in category_dict.items():
            category_count = category_info["count"]
            category_ratio = round(category_count / total_students, 4)
            
            # 对二级分类明细按人数降序排序
            sorted_details = sorted(
                category_info["details"],
                key=lambda x: x["人数"],
                reverse=True
            )
            
            data.append({
                "一级分类": merge_type,
                "一级人数": category_count,
                "一级占比": category_ratio,
                "明细": sorted_details
            })
        
        # 按一级人数降序排序
        data.sort(key=lambda x: x["一级人数"], reverse=True)
        
        result_data = [{
            "group": "总体",
            "data": data,
            "total": total_students
        }]
        
        logger.info(f"指标 '毕业去向分布各类型总体占比' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_data
        }

    except Exception as e:
        logger.error(f"计算指标 '毕业去向分布各类型总体占比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 毕业去向分布各类型总体占比",
            "code": 500,
            "error": str(e)
        }